# coding=utf8

import sqlite3
import pymysql
import faker
import time


class Preliminary:

    @staticmethod
    def create_conn(dbdriver=sqlite3,
                    dbname='testdb',
                    host='localhost',
                    username='root',
                    password='123456',
                    port=3306):
        conn = None
        if dbdriver == pymysql:
            conn = pymysql.connect(
                host=host,
                port=port,
                user=username,
                password=password,
                database=dbname
            )
        if dbdriver == sqlite3:
            conn = sqlite3.connect(dbname)
        return conn

    @staticmethod
    def create_table(cursor, table_name='test64'):
        table_name = table_name
        sql = """DROP TABLE IF EXISTS {}"""
        cursor.execute(sql.format(table_name))
        sql = f"""
        CREATE TABLE {table_name} (id int, name char(10), address varchar(100), phone char(11))
        """
        cursor.execute(sql)

    @staticmethod
    def insert_into_mode1():
        fk = faker.Faker('zh_cn')

        conn = Preliminary.create_conn()
        cursor = conn.cursor()

        table_name = 'test64a'
        Preliminary.create_table(cursor, table_name)

        # insert one by one
        stime = time.time()
        for j in range(10000):
            sql = f"INSERT INTO {table_name} "
            sql = sql + """VALUES ({}, '{}', '{}', '{}')"""
            sql = sql.format(j, fk.name(), fk.address(), fk.phone_number())
            # print(sql)
            cursor.execute(sql)
        print("mode_2 resumed time = {:.4f}".format(time.time()-stime))
        # show result
        Preliminary.select_somerows_from_table(cursor, table_name=table_name, count=3)
        conn.commit()
        cursor.close()
        conn.close()

    @staticmethod
    def insert_into_mode2():
        fk = faker.Faker('zh_cn')

        conn = Preliminary.create_conn()
        cursor = conn.cursor()

        table_name = 'test64b'
        Preliminary.create_table(cursor, table_name)

        # insert one by one
        stime = time.time()
        for j in range(10000):
            sql = f"INSERT INTO {table_name} "
            sql = sql + """VALUES ({}, '{}', '{}', '{}')"""
            sql = sql.format(j, fk.name(), fk.address(), fk.phone_number())
            # print(sql)
            cursor.execute(sql)
        print("mode_2 resumed time = {:.4f}".format(time.time()-stime))
        # show result
        Preliminary.select_somerows_from_table(cursor, table_name=table_name, count=3)
        conn.commit()
        cursor.close()
        conn.close()

    @staticmethod
    def insert_into_mode3():
        fk = faker.Faker('zh_cn')
        conn = Preliminary.create_conn()
        cursor = conn.cursor()

        table_name = 'test64c'
        # 创建数据表
        Preliminary.create_table(cursor, table_name)
        # 批量插入数据
        sql = f"INSERT INTO {table_name} VALUES "
        record_value = "({}, '{}', '{}', '{}'),"
        for i in range(10000):
            sql += record_value.format(i, fk.name(), fk.address(), fk.phone_number())
        sql = sql[:-1]
        stime = time.time()
        cursor.execute(sql)
        print("mode_3 resumed time = {:.4f}".format(time.time()-stime))
        # 显示插入数据
        Preliminary.select_somerows_from_table(cursor, table_name, count=3)

    @staticmethod
    def select_somerows_from_table(cursor=None, table_name=None, count=10):
        sql = """select * from {}""".format(table_name)
        cursor.execute(sql)
        print("[{}]".format(table_name))
        for row in cursor.fetchmany(count):
            print(row)


def task():
    conn = sqlite3.connect("testdb")
    cursor = conn.cursor()

    table_name = "table64d"
    Preliminary.create_table(cursor, table_name)

    fk = faker.Faker('zh_cn')
    data = []
    for i in range(10000):
        data.append((i, fk.name(), fk.address(), fk.phone_number()))

    sql = "INSERT INTO {} VALUES ".format(table_name)
    for d in data:
        sql += f"({d[0]}, '{d[1]}', '{d[2]}', '{d[3]}'),"
    # 去除最后一个逗号
    sql = sql[:-1]
    cursor.execute(sql)

    cursor.execute("SELECT * from {}".format(table_name))
    print(cursor.fetchall())
    print(cursor.rowcount)

    # 打印输出批量插入数据的前5条记录
    Preliminary.select_somerows_from_table(cursor, table_name, 5)

    # 关闭游标和链接
    cursor.close()
    conn.close()


def expand1():
    fk = faker.Faker('zh_cn')
    import time

    table_name = "table64e"
    insert_count = 100

    conn = sqlite3.connect("testdb")
    cursor = conn.cursor()
    Preliminary.create_table(cursor, table_name)
    cursor.close()
    conn.close()

    st = time.time()
    for i in range(insert_count):
        conn = sqlite3.connect("testdb")
        cursor = conn.cursor()
        # insert
        data = (i, fk.name(), fk.address(), fk.phone_number())
        sql = "INSERT INTO {} VALUES ({}, '{}', '{}', '{}')".format(table_name, *data)
        cursor.execute(sql)
        cursor.execute("commit")    # 关闭连接太频繁，数据不能写入数据库
        # close
        cursor.close()
        conn.close()
    print("consume: {:.3f}".format(time.time()-st))

    # 打印输出批量插入数据的前5条记录
    conn = sqlite3.connect("testdb")
    cursor = conn.cursor()
    query = cursor.execute("SELECT * from {}".format(table_name))
    for row in query.fetchmany(5):
        print(row)
    cursor.close()
    conn.close()


if __name__ == "__main__":
    # Preliminary.insert_into_mode2()
    # Preliminary.insert_into_mode3()
    # task()
    expand1()

